/**
 * SQL解析器库
 * 提供基本的SQL语法解析功能
 */

class SQLParser {
    /**
     * 解析CREATE TABLE语句
     * @param {string} sql - SQL语句
     * @returns {Object|null} 解析结果对象或null
     */
    static parseCreateTable(sql) {
      try {
        // 移除注释
        sql = sql.replace(/--.*$/gm, '').replace(/\/\*[\s\S]*?\*\//g, '');
        
        // 匹配CREATE TABLE语句
        const createTableRegex = /CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?[`"\[]?(\w+)[`"\]]?\s*\(([\s\S]*)\)(?:\s*ENGINE\s*=\s*(\w+))?(?:\s*DEFAULT\s+CHARSET\s*=\s*(\w+))?(?:\s*COMMENT\s*=\s*['"](.+?)['"])?/i;
        const match = sql.match(createTableRegex);
        
        if (!match) {
          return null;
        }
        
        const tableName = match[1];
        const columnsDefinition = match[2];
        
        // 优先使用主正则捕获到的表注释；若未捕获，再从整条语句中二次提取
        let tableComment = match[5] || '';
        
        if (!tableComment) {
          const commentSearch = /COMMENT\s*=\s*['"]([^'"]+)['"]/i;
          const cMatch = sql.match(commentSearch);
          if (cMatch) {
            tableComment = cMatch[1];
          }
        }
        
        // 处理列定义和表约束
        const parts = this._splitColumnDefinitions(columnsDefinition);
        const columns = [];
        const constraints = [];
        
        // 解析列定义与约束（先判断约束，避免将 PRIMARY KEY 等误判为列）
        for (const part of parts) {
          if (this._isConstraint(part)) {
            const constraint = this._parseConstraint(part, columns);
            constraints.push(constraint);
          } else if (this._isColumnDefinition(part)) {
            const column = this._parseColumnDefinition(part);
            columns.push(column);
          }
        }
        
        // 应用约束信息到列定义
        for (const constraint of constraints) {
          if (constraint.type === 'PRIMARY KEY') {
            for (const column of columns) {
              if (constraint.columns.includes(column.columnName)) {
                column.keyType = 'PRI';
              }
            }
          } else if (constraint.type === 'UNIQUE') {
            for (const column of columns) {
              if (constraint.columns.includes(column.columnName) && !column.keyType) {
                column.keyType = 'UNI';
              }
            }
          } else if (constraint.type === 'FOREIGN KEY') {
            for (const column of columns) {
              if (constraint.columns.includes(column.columnName) && !column.keyType) {
                column.keyType = 'MUL';
              }
            }
          }
        }
        
        // 解析索引信息
        for (const part of parts) {
          if (part.toUpperCase().includes('KEY') && !part.toUpperCase().includes('PRIMARY KEY') && !part.toUpperCase().includes('FOREIGN KEY')) {
            const keyRegex = /KEY\s+[`"]?(\w+)?[`"]?\s*\(([^)]+)\)/i;
            const keyMatch = part.match(keyRegex);
            
            if (keyMatch) {
              const keyColumns = keyMatch[2]
                .split(',')
                .map(col => col.trim().replace(/[`"'\[\]]/g, ''));
                
              for (const column of columns) {
                if (keyColumns.includes(column.columnName) && !column.keyType) {
                  column.keyType = 'MUL';
                }
              }
            }
          }
        }
        
        return {
          tableName,
          columns,
          constraints,
          comment: tableComment
        };
      } catch (error) {
        console.error('SQL解析错误:', error);
        return null;
      }
    }
    
    /**
     * 拆分列定义字符串
     * @private
     * @param {string} columnsDefinition - 列定义字符串
     * @returns {Array<string>} 列定义数组
     */
    static _splitColumnDefinitions(columnsDefinition) {
      const parts = [];
      let currentPart = '';
      let parenLevel = 0;
      let inString = false;
      let stringChar = '';
      
      for (let i = 0; i < columnsDefinition.length; i++) {
        const char = columnsDefinition[i];
        
        // 处理字符串
        if ((char === "'" || char === '"') && (i === 0 || columnsDefinition[i-1] !== '\\')) {
          if (!inString) {
            inString = true;
            stringChar = char;
          } else if (stringChar === char) {
            inString = false;
          }
        }
        
        // 处理括号
        if (!inString) {
          if (char === '(') parenLevel++;
          else if (char === ')') parenLevel--;
        }
        
        // 处理分隔符
        if (char === ',' && parenLevel === 0 && !inString) {
          parts.push(currentPart.trim());
          currentPart = '';
        } else {
          currentPart += char;
        }
      }
      
      if (currentPart.trim()) {
        parts.push(currentPart.trim());
      }
      
      return parts;
    }
    
    /**
     * 判断字符串是否为列定义
     * @private
     * @param {string} str - 待判断的字符串
     * @returns {boolean} 是否为列定义
     */
    static _isColumnDefinition(str) {
      // 以标识符开头，后面跟着数据类型
      return /^\s*[`"\[]?\w+[`"\]]?\s+\w+/i.test(str);
    }
    
    /**
     * 判断字符串是否为约束定义
     * @private
     * @param {string} str - 待判断的字符串
     * @returns {boolean} 是否为约束定义
     */
    static _isConstraint(str) {
      const upperStr = str.toUpperCase();
      return upperStr.includes('PRIMARY KEY') || 
             upperStr.includes('UNIQUE') || 
             upperStr.includes('FOREIGN KEY') || 
             upperStr.startsWith('CONSTRAINT');
    }
    
    /**
     * 解析列定义
     * @private
     * @param {string} columnDef - 列定义字符串
     * @returns {Object} 列信息对象
     */
    static _parseColumnDefinition(columnDef) {
      // 解析字段名
      const nameRegex = /^[`"\[]?(\w+)[`"\]]?/;
      const nameMatch = columnDef.match(nameRegex);
      const columnName = nameMatch ? nameMatch[1] : '';
      
      // 解析数据类型
      const typeRegex = /\s+(\w+)(?:\(([^)]+)\))?/;
      const typeMatch = columnDef.match(typeRegex);
      let dataType = '';
      let typeLength = '';
      
      if (typeMatch) {
        dataType = typeMatch[1];
        typeLength = typeMatch[2] || '';
        dataType = dataType + (typeLength ? `(${typeLength})` : '');
      }
      
      // 解析是否为空
      const isNullable = columnDef.toUpperCase().includes('NOT NULL') ? 'NO' : 'YES';
      
      // 解析主键
      const isPrimary = columnDef.toUpperCase().includes('PRIMARY KEY') ? 'PRI' : '';
      
      // 解析默认值
      let defaultValue = '';
      const defaultRegex = /DEFAULT\s+(?:'([^']*)'|"([^"]*)"|(\w+))/i;
      const defaultMatch = columnDef.match(defaultRegex);
      
      if (defaultMatch) {
        defaultValue = defaultMatch[1] || defaultMatch[2] || defaultMatch[3] || '';
        
        // 特殊处理CURRENT_TIMESTAMP
        if (defaultValue.toUpperCase() === 'CURRENT_TIMESTAMP') {
          defaultValue = 'CURRENT_TIMESTAMP';
        } else if (defaultValue.toUpperCase() === 'NULL') {
          defaultValue = 'NULL';
        }
      }
      
      // 解析注释/说明
      let comment = '';
      const commentRegex = /COMMENT\s+['"](.+?)['"]/i;
      const commentMatch = columnDef.match(commentRegex);
      
      if (commentMatch) {
        comment = commentMatch[1];
      }
      
      return {
        columnName,
        dataType,
        isNullable,
        keyType: isPrimary,
        defaultValue,
        comment
      };
    }
    
    /**
     * 解析约束定义
     * @private
     * @param {string} constraintDef - 约束定义字符串
     * @param {Array<Object>} columns - 列定义数组
     * @returns {Object} 约束信息对象
     */
    static _parseConstraint(constraintDef, columns) {
      const upperDef = constraintDef.toUpperCase();
      
      // 提取约束名称（如果有）
      let constraintName = '';
      const nameRegex = /CONSTRAINT\s+[`"]?(\w+)[`"]?/i;
      const nameMatch = constraintDef.match(nameRegex);
      if (nameMatch) {
        constraintName = nameMatch[1];
      }
      
      // 解析主键约束
      if (upperDef.includes('PRIMARY KEY')) {
        const columnsRegex = /PRIMARY\s+KEY\s*\(([^)]+)\)/i;
        const match = constraintDef.match(columnsRegex);
        
        if (match) {
          const columnList = match[1]
            .split(',')
            .map(col => col.trim().replace(/[`"'\[\]]/g, ''));
            
          return {
            name: constraintName,
            type: 'PRIMARY KEY',
            columns: columnList
          };
        }
      }
      
      // 解析唯一约束
      if (upperDef.includes('UNIQUE')) {
        const columnsRegex = /UNIQUE(?:\s+KEY|\s+INDEX)?\s*(?:[`"]?\w+[`"]?)?\s*\(([^)]+)\)/i;
        const match = constraintDef.match(columnsRegex);
        
        if (match) {
          const columnList = match[1]
            .split(',')
            .map(col => col.trim().replace(/[`"'\[\]]/g, ''));
            
          return {
            name: constraintName,
            type: 'UNIQUE',
            columns: columnList
          };
        }
      }
      
      // 解析外键约束
      if (upperDef.includes('FOREIGN KEY')) {
        const fkRegex = /FOREIGN\s+KEY\s*\(([^)]+)\)\s+REFERENCES\s+[`"]?(\w+)[`"]?\s*\(([^)]+)\)(?:\s+ON\s+DELETE\s+(\w+))?(?:\s+ON\s+UPDATE\s+(\w+))?/i;
        const match = constraintDef.match(fkRegex);
        
        if (match) {
          const columnList = match[1]
            .split(',')
            .map(col => col.trim().replace(/[`"'\[\]]/g, ''));
          
          const refTable = match[2];
          const refColumnList = match[3]
            .split(',')
            .map(col => col.trim().replace(/[`"'\[\]]/g, ''));
            
          return {
            name: constraintName,
            type: 'FOREIGN KEY',
            columns: columnList,
            refTable,
            refColumns: refColumnList,
            onDelete: match[4] || '',
            onUpdate: match[5] || ''
          };
        }
      }
      
      // 解析普通索引
      if (upperDef.includes('KEY') || upperDef.includes('INDEX')) {
        const keyRegex = /(?:KEY|INDEX)\s+[`"]?(\w+)[`"]?\s*\(([^)]+)\)/i;
        const match = constraintDef.match(keyRegex);
        
        if (match) {
          const keyName = match[1];
          const columnList = match[2]
            .split(',')
            .map(col => col.trim().replace(/[`"'\[\]]/g, ''));
            
          return {
            name: keyName,
            type: 'INDEX',
            columns: columnList
          };
        }
      }
      
      return {
        name: constraintName,
        type: 'UNKNOWN',
        definition: constraintDef
      };
    }
    
    /**
     * 解析包含多条 CREATE TABLE 语句的 SQL
     * @param {string} sql - SQL 内容，可包含多张表
     * @returns {Array<Object>} 每张表的解析结果数组
     */
    static parseCreateTables(sql) {
      const results = [];
      try {
        // 预先去除块注释与单行注释，避免干扰匹配
        sql = sql.replace(/--.*$/gm, '').replace(/\/\*[\s\S]*?\*\//g, '');

        // 使用非贪婪匹配，以分号作为结束标识，循环提取每条建表语句
        const createTableStmtRegex = /CREATE\s+TABLE[\s\S]*?;(?=\s|$)/ig;
        let match;

        while ((match = createTableStmtRegex.exec(sql)) !== null) {
          const stmt = match[0].trim();
          // 去掉末尾分号，防止影响单条解析
          const cleanStmt = stmt.endsWith(';') ? stmt.slice(0, -1) : stmt;
          const tableInfo = this.parseCreateTable(cleanStmt);
          if (tableInfo) {
            results.push(tableInfo);
          }
        }
      } catch (err) {
        console.error('批量解析 SQL 出错:', err);
      }
      return results;
    }
  }
  
  // 如果在浏览器环境中，将SQLParser绑定到window对象
  if (typeof window !== 'undefined') {
    window.SQLParser = SQLParser;
  }
  
  // 如果在Node.js环境中，导出SQLParser
  if (typeof module !== 'undefined' && module.exports) {
    module.exports = SQLParser;
  }